﻿Imports System.data 
Imports System.data.SqlClient 
Imports System.Configuration



Public Class CBaseDeDatosSQLServer


Private cnnstr As String      
Private cnx As SqlConnection    


	Public Sub New()
		
        Me.cnnstr = CEncripcion.Decrypt(ConfigurationManager.ConnectionStrings("SqlConexion").ConnectionString, "AJnx8d73") 'Se obtiene la cadena de conexión		

	End Sub


    Public Function CrearConexion(ByRef sMsgError As String) As SqlConnection 
		Try
			cnx = New SqlConnection (Me.cnnstr )
		Catch ex As SqlException 
		    sMsgError = ex.Message  
			cnx = Nothing
		End Try
		Return cnx

	End Function


    Private Sub CargarParametros(ByRef pm As List(Of SqlParameter ), ByRef cmd As SqlCommand  )    
		cmd.Parameters.Clear()
		For Each parametro As sqlParameter  In pm
			cmd.Parameters.Add(parametro)
		Next
	End Sub
   

    Private Sub RegresarParametros(ByRef pm As List(Of sqlParameter), ByRef cmd As sqlCommand , ByVal intParamIgnorados As Integer)	
		pm.Clear()
		For i As Integer = 0 To (cmd.Parameters.Count - intParamIgnorados - 1)
				pm.Add(cmd.Parameters.Item(i))
		Next
	End Sub


    Private Function GenerarCadenaParamSP(ByVal intNumParam As Integer) As String

		Dim sCadenaRet As String
		Dim i As Integer

		sCadenaRet = "("
		For i = 1 To intNumParam
			sCadenaRet = sCadenaRet & "?,"
		Next

		If sCadenaRet.Length() > 1 Then
		   sCadenaRet = sCadenaRet.Substring(0, sCadenaRet.Length - 1)
		End If

		sCadenaRet = sCadenaRet & ")"

		Return sCadenaRet
	End Function


    Public Function EjecutarConsulta(ByVal sSql As String, ByRef pm As List(Of sqlParameter), ByRef sMsgError As String) As DataTable
	
		Dim rd		As SqlDataReader  
		Dim dt		As DataTable
		Dim cmd		As sqlCommand 
		Dim dbCnn   As sqlConnection 

		sMsgError = ""


		'1) Se valida la creación y apertura de la conexión a la base de datos
		dbCnn = CrearConexion(sMsgError)
		If Not dbCnn Is Nothing Then
			Try
				 dbCnn.Open()
			Catch ex As Exception
			   dbCnn.Dispose()
			   sMsgError = ex.Message
			   Return Nothing
			End Try
		Else
		   Return Nothing
		End If


		'Se ejecuta la consulta sql con sus parámetros

		 cmd = New sqlCommand 
		 Try
			cmd.CommandType = CommandType.Text
			cmd.Connection = dbCnn
			cmd.CommandTimeout = 0

			cmd.CommandText = sSql
			CargarParametros(pm, cmd)

			rd = cmd.ExecuteReader()

			dt = New DataTable()
			dt.Load(rd)
			rd.Close()

		Catch ex As Exception

			sMsgError = ex.Message
			Return Nothing

		Finally

		   dbCnn.Close()
		   dbCnn.Dispose()
		   cmd.Dispose()
		End Try

		Return dt
	End Function


    Public Function EjecutarComando(ByVal sSql As String, ByRef pm As List(Of sqlParameter), ByRef afectados As Integer, ByRef sMsgError As String) As Boolean
	

		Dim dbCnn   As sqlConnection 
		Dim dbTrans As sqlTransaction 
		Dim cmd     As sqlCommand 

		sMsgError = ""
		afectados = 0

        
    

		'1) Se valida la creación y apertura de la conexión a la base de datos
		dbCnn = CrearConexion(sMsgError)
		If Not dbCnn Is Nothing Then
			Try
				 dbCnn.Open()
			Catch ex As Exception
			   dbCnn.Dispose()
			   sMsgError = ex.Message
			   Return False
			End Try
		Else
		   Return false
		End If


		'2) Se establece una transacción 
		dbTrans = dbCnn.BeginTransaction(IsolationLevel.ReadCommitted)

		'3) Se ejecuta el comando  o bloque de comandos 
		cmd = New SqlCommand  
		Try
			cmd.CommandType = CommandType.Text
			cmd.Connection = dbCnn
			cmd.CommandTimeout = 0

			cmd.CommandText = sSql
			CargarParametros(pm, cmd)


			cmd.Transaction = dbTrans
			afectados = cmd.ExecuteNonQuery()
			dbTrans.Commit()   'Se completa la transacción			

		Catch ex As Exception
			dbTrans.Rollback()	'En caso de error se reversa las operaciones realizadas		
			sMsgError = ex.Message
			Return False

		Finally
			dbCnn.Close()
			dbCnn.Dispose()
			dbTrans.Dispose()
			cmd.Dispose()

		End Try

		Return True

	End Function
    

    Public Function EjecutarSPConsulta(ByVal sNombreSP As String, ByRef pm As List(Of sqlParameter ), ByRef sMsgError As String) As DataTable
	

		Dim rd       As sqlDataReader 
		Dim dt       As New DataTable
		Dim dbCnn    As sqlConnection 
		Dim dbTrans  As sqlTransaction 
		Dim cmd      As sqlCommand 

		sMsgError = ""



		'1) Se valida la creación y apertura de la conexión a la base de datos
		dbCnn = CrearConexion(sMsgError)
		If Not dbCnn Is Nothing Then
			Try
				 dbCnn.Open()
			Catch ex As Exception
			   dbCnn.Dispose()
			   sMsgError = ex.Message
			   Return Nothing
			End Try
		Else
		   Return Nothing
		End If


		'2) Se establece una transacción 
		dbTrans = dbCnn.BeginTransaction(IsolationLevel.ReadCommitted)


		'Se ejecuta la consulta sql con sus parámetros
		cmd = New sqlCommand ()
		Try
			cmd.CommandType = CommandType.Text
			cmd.Connection = dbCnn
			cmd.CommandTimeout = 0


			Dim parm As sqlParameter 
			CargarParametros(pm, cmd)

			'Se agregan dos parámetros para el manejo de excepciones dentro del SP, los cuales son adicionales a los párametros entrada originales. 

			parm = cmd.Parameters.Add(New sqlParameter("@SQL_CODRET", SqlDbType.Int))

             
			parm.Direction = ParameterDirection.Output

			parm = cmd.Parameters.Add(New sqlParameter("@SQL_MSGERROR", sqlDbType.VarChar, 30))
			parm.Direction = ParameterDirection.Output

			cmd.CommandText = "CALL " & sNombreSP & GenerarCadenaParamSP(cmd.Parameters.Count)


			cmd.Transaction = dbTrans
			rd = cmd.ExecuteReader()
		                 
			
			Dim intSQLCodret As Integer = CType(cmd.Parameters("@SQL_CODRET").Value, Int32)
			Dim strSQLMsgError As String = CType(cmd.Parameters("@SQL_MSGERROR").Value, String)


			RegresarParametros(pm, cmd, 2)


			If intSQLCodret = 0 Then
				dt.Load(rd)
				rd.Close()
				dbTrans.Commit()
			Else
				dbTrans.Rollback()
				sMsgError = "Error: SQLCODE " & intSQLCodret.ToString() & ",  " & strSQLMsgError
				dt = Nothing
			End If


		Catch ex As Exception
			sMsgError = ex.Message
			Return Nothing
		Finally
		   dbCnn.Close()
		   dbCnn.Dispose()
		End Try

		Return dt
	End Function


   Public Function EjecutarSPComandos(ByVal sNombreSP As String, ByRef pm As List(Of sqlParameter), ByRef sMsgError As String) As Boolean 
	
			
		Dim dbCnn   As sqlConnection 
		Dim dbTrans As sqlTransaction 
		Dim cmd     As sqlCommand 

		sMsgError = ""


		'1) Se valida la creación y apertura de la conexión a la base de datos
		dbCnn = CrearConexion(sMsgError)
		If Not dbCnn Is Nothing Then
			Try
				 dbCnn.Open()
			Catch ex As Exception
			   dbCnn.Dispose()
			   sMsgError = ex.Message
			   Return False 
			End Try
		Else
		   Return False 
		End If


		'2) Se establece una transacción 
		dbTrans = dbCnn.BeginTransaction(IsolationLevel.ReadCommitted)


		'Se ejecuta la consulta sql con sus parámetros
		cmd = New sqlCommand 
		Try
			cmd.CommandType = CommandType.StoredProcedure 
			cmd.Connection = dbCnn
			cmd.CommandTimeout = 0


			Dim parm As sqlParameter 
			CargarParametros(pm, cmd)
			parm = cmd.Parameters.Add(New sqlParameter("@SQL_CODRET",  sqlDbType.Int))
			parm.Direction = ParameterDirection.Output
			parm = cmd.Parameters.Add(New sqlParameter("@SQL_MSGERROR", sqlDbType.VarChar, 60))
			parm.Direction = ParameterDirection.Output



			cmd.CommandText =  sNombreSP 


			cmd.Transaction = dbTrans
			cmd.ExecuteNonQuery ()


			Dim intSQLCodret As Integer = CType(cmd.Parameters("@SQL_CODRET").Value, Int32)
			Dim strSQLMsgError As String = CType(cmd.Parameters("@SQL_MSGERROR").Value, String)

			'Se eliminan los dos parámetros adicionales para el manejo de excepciones y sólo se mantienen los parámetros de entrada oriigiales.
			RegresarParametros(pm, cmd, 2)


			If intSQLCodret = 0 Then				
				dbTrans.Commit()
				Return true
			Else
				dbTrans.Rollback()
				sMsgError = "Error: SQLCODE " & intSQLCodret.ToString() & ",  " & strSQLMsgError	
				Return False 			
			End If

		Catch ex As Exception
		    dbTrans.Rollback()
			sMsgError = ex.Message
			Return false
		Finally
		   dbCnn.Close()
		   dbCnn.Dispose()
		End Try

		
	End Function










End Class
